﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.Win32;
using System.Data.SqlClient;
using System.IO;

namespace CommonsLib.ORMTool
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }


        #region 把基本信息写入注册表
        private void SetRegister()
        {
             
                RegistryKey rk = Registry.CurrentUser;
                RegistryKey rkVB = rk.CreateSubKey("CommonsLib.ORMTool");

                rkVB.SetValue("ServerName", txtServerName.Text);
                rkVB.SetValue("DBName", txtDBName.Text);
                rkVB.SetValue("UserName", txtUserName.Text);
                rkVB.SetValue("Password", txtPassword.Text);

                rkVB.SetValue("OutPutFilePath", txtFilePath.Text);
                rkVB.SetValue("NameSpace", TxtNameSpace.Text);
 
                rkVB.SetValue("DELPostfix", txtDELPostfix.Text);

           
        }

        private void GetRegister()
        {
             
                RegistryKey rk = Registry.CurrentUser;
                RegistryKey rkVB = rk.OpenSubKey("CommonsLib.ORMTool");
                if (rkVB != null)
                {
                    txtServerName.Text = rkVB.GetValue("ServerName").ToString();
                    txtDBName.Text = rkVB.GetValue("DBName").ToString();
                    txtUserName.Text = rkVB.GetValue("UserName").ToString();
                    txtPassword.Text = rkVB.GetValue("Password").ToString();
                    txtFilePath.Text = rkVB.GetValue("OutPutFilePath").ToString();
                    TxtNameSpace.Text = rkVB.GetValue("NameSpace").ToString();
 
                    txtDELPostfix.Text = rkVB.GetValue("DELPostfix").ToString();
                }
             
        }
        #endregion

        private void Form1_Load(object sender, EventArgs e)
        {
            GetRegister();
        }

        private void btnLoad_Click(object sender, EventArgs e)
        {
             
                if (txtServerName.Text == "")
                {
                    MessageBox.Show("Input server name please!");
                    txtServerName.Focus();
                    return;
                }
                if (txtDBName.Text == "")
                {
                    MessageBox.Show("Input Datebase name please!");
                    txtDBName.Focus();
                    return;
                }
                

            try
            {
                
                FillTableAndViewTree(listBox1);

                //把基本信息写入注册表
                SetRegister();

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void FillTableAndViewTree(ListBox lst)
        {
           
			
			
			DataTable DT=GetTableOrViewList();
			DataView DV=new DataView(DT);

			try
			{
				lst.BeginUpdate ();

				lst.Items.Clear();
 
				
				DV.RowFilter="xtype='u'";

				foreach(DataRowView DRV  in DV)
				{
					
                    lst.Items.Add(DRV[0].ToString());
				}
				
				


				lst.EndUpdate();
			}
			catch(Exception e)
			{
				lst.EndUpdate();
                MessageBox.Show(e.Message); 
			}
		}

        private DataTable GetTableOrViewList()
        {
            string sql = "select Name,xtype FROM sysobjects WHERE ";
            sql += "xtype = 'u' order by name";

            SqlConnection con = new SqlConnection(GetConnectionString());
            SqlDataAdapter dap = new SqlDataAdapter(sql, con);

            DataSet DS = new DataSet();
            dap.Fill(DS);

            return DS.Tables[0];
        }

        

        private void button1_Click(object sender, EventArgs e)
        {
            CreateModelFile();
        }

        private void CreateModelFile()
        {
            if (listBox1.SelectedItem == null)
            {
                MessageBox.Show("Please select table");
                return;
            }
            string strTableName=listBox1.SelectedItem.ToString();

            const string strTemplate = @"
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.Serialization;
 
namespace {NameSpace}
{
{Tab}[Serializable]
{Tab}[DataContract]
{Tab}[CommonsLib.ORM.DataTable(DBName = ""{DBName}"", TableName = ""{TableName}"", KeyPropName = ""Key"", ForeignKeyPropName = ""ForeignKey"")]
{Tab}public class {ModelName}:BaseDataModel
{Tab}{
{Fields}
{Tab}{Tab}public override string ToString()
{Tab}{Tab}{
{Tab}{Tab}{Tab}return string.Format(""{0}.{1}"",""{TableName}"",this.Key);
{Tab}{Tab}}
{Tab}}
}
            ";

            string sql = string.Format(@"SELECT TOP 100 PERCENT dbo.syscolumns.name AS FieldName, 
						dbo.systypes.name AS Type, dbo.syscolumns.length, dbo.syscolumns.isnullable, 
						dbo.syscomments.text AS defaultValue,
							(SELECT COUNT(*)
							FROM sysindexkeys
							WHERE sysindexkeys.indid = 1 AND sysindexkeys.id = dbo.syscolumns.id AND 
								sysindexkeys.colid = dbo.syscolumns.colid) AS isKey, 
						dbo.syscolumns.colstat as IsIncrement
					FROM dbo.syscolumns INNER JOIN
						dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype INNER JOIN
						dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id LEFT OUTER JOIN
						dbo.syscomments ON dbo.syscolumns.cdefault = dbo.syscomments.id
					WHERE (dbo.sysobjects.name = '{0}')
					ORDER BY dbo.syscolumns.colid" ,strTableName);

            SqlConnection con = new SqlConnection(GetConnectionString());
            SqlDataAdapter dap = new SqlDataAdapter(sql, con);

            DataSet DS = new DataSet();
            dap.Fill(DS);

            string strKeyName = string.Empty;

            StringBuilder strFileds = new StringBuilder();
            StringBuilder strFileds_ToExcel = new StringBuilder();
            foreach(DataRow dr in DS.Tables[0].Rows)
            {
                if (dr["Type"].ToString().Equals("sysname")) continue;
                strFileds.Append("{Tab}{Tab}[DataMember]");
                strFileds.AppendLine();
                if (dr["IsIncrement"].ToString().Equals("1"))
                {
                    strKeyName = dr["FieldName"].ToString().TrimEnd();
                    strFileds.AppendFormat(@"{{Tab}}{{Tab}}[CommonsLib.ORM.DataField(""{0}"", true)]", dr["FieldName"].ToString().TrimEnd());
                }
                else
                {
                    strFileds.AppendFormat(@"{{Tab}}{{Tab}}[CommonsLib.ORM.DataField(""{0}"", ""{1}"", {2})]", dr["FieldName"].ToString().TrimEnd(), dr["Type"],GetLength(dr["Type"].ToString(), dr["length"].ToString()));
                }                
                strFileds.AppendLine();

                strFileds_ToExcel.AppendFormat("{0}\r\n", dr["FieldName"]);

                if (dr["isKey"].ToString().Equals("1") && (strKeyName.Length == 0 || strKeyName.Equals("recid", StringComparison.InvariantCultureIgnoreCase )))
                {
                    strKeyName = dr["FieldName"].ToString().TrimEnd();
                    strFileds.AppendFormat(@"{{Tab}}{{Tab}}public {1} Key {{get;set;}}", dr["FieldName"].ToString().TrimEnd(), GetTypeName(dr["Type"].ToString()));
                }
                else
                {
                    strFileds.AppendFormat(@"{{Tab}}{{Tab}}public {1} {0} {{get;set;}}", dr["FieldName"].ToString().TrimEnd(), GetTypeName(dr["Type"].ToString()));
                }
                strFileds.AppendLine();

                strFileds.AppendLine();

                
            }

            using (StreamWriter sw = new StreamWriter(string.Format(@"{0}{1}{2}.cs", txtFilePath.Text, strTableName,txtDELPostfix.Text), false))
            {


                string str = strTemplate.Replace("{NameSpace}",TxtNameSpace.Text);
                str = str.Replace("{DBName}",txtDBName.Text);
                str = str.Replace("{TableName}", strTableName);
                str = str.Replace("{key}", strKeyName);
                str = str.Replace("{ModelName}", string.Format(@"{0}{1}", strTableName,txtDELPostfix.Text));
                str = str.Replace("{Fields}", strFileds.ToString());

                while (str.IndexOf("{Tab}") > 0)
                {
                    str = str.Replace("{Tab}", "    ");
                }

                sw.Write(str);

            }
            //using (StreamWriter sw = new StreamWriter(string.Format(@"{0}{1}.txt", txtFilePath.Text, strTableName, txtDELPostfix.Text), false))
            //{



            //    sw.Write(strFileds_ToExcel.ToString());

            //}
            MessageBox.Show(string.Format("{0} OK!",strTableName ));
        }

        private string GetTypeName(string strType)
        {
            if (strType.Equals("int", StringComparison.InvariantCultureIgnoreCase ))
            {
                return "int";
            }
            else if (strType.Equals("decimal", StringComparison.InvariantCultureIgnoreCase))
            {
                return "double";
            }
            else if (strType.Equals("datetime", StringComparison.InvariantCultureIgnoreCase))
            {
                return "DateTime";
            }
            else if (strType.Equals("bit", StringComparison.InvariantCultureIgnoreCase))
            {
                return "bool";
            }
            else if (strType.Equals("timestamp", StringComparison.InvariantCultureIgnoreCase))
            {
                return "byte[]";
            }

            return "string";
        }

        private object GetLength(string strType, string strLength)
        {
            if(strType.StartsWith("n"))
            {
                return int.Parse(strLength)/2;
            }
            else
            {
                return strLength;
            }
        }

        private string GetConnectionString()
        {
            string strCon=string.Empty;
            if (txtUserName.Text.Length == 0)
            {
                strCon+="Trusted_Connection=yes";
            }
            else
            {
                strCon += "uid=" + txtUserName.Text + ";pwd=" + txtPassword.Text;
            }
            strCon+=";data source=" + txtServerName.Text + ";database=" + txtDBName.Text;

            return strCon;
        }

        private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.Text = listBox1.SelectedItem.ToString();
        }

        
       
    }
}
